package com.lcj.ems.dao.impl;

import com.lcj.ems.dao.EmpDao;
import com.lcj.ems.entity.Emp;
import com.lcj.ems.exception.DataAccessException;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class EmpDaoImpl implements EmpDao {
    private Connection conn;
    private PreparedStatement pstmt;
    private ResultSet rs;
    private static final String DATABASE_DRIVER = "com.mysql.jdbc.Driver";
    private static final String  GLOBLE_DATABASE_EMP_URL="jdbc:mysql://127.0.0.1:3306/ems?useUnicode=true&characterEncoding=utf-8";
    private static final String  GLOBLE_DATABASE_EMP_USERNAME="root";
    private static final String  GLOBLE_DATABASE_EMP_PASSWORD="";

    @Override
    public void insertEmp(Emp emp) throws DataAccessException {
        try {
            Class.forName(DATABASE_DRIVER);
            conn = DriverManager.getConnection(GLOBLE_DATABASE_EMP_URL, GLOBLE_DATABASE_EMP_USERNAME, GLOBLE_DATABASE_EMP_PASSWORD);
            String sql = new StringBuffer()
                    .append("insert into t_emp ")
                    .append(" (name,salary,gender,edu,hobbies) ")
                    .append(" values ")
                    .append(" (?,?,?,?,?) ").toString();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,emp.getName());
            pstmt.setDouble(2,emp.getSalary());
            pstmt.setString(3,emp.getGender());
            pstmt.setString(4,emp.getEdu());
            pstmt.setString(5,emp.getHob());
            pstmt.executeUpdate();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            throw new DataAccessException("驱动器加载失败异常");
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataAccessException("获取数据库连接异常");
        }
    }

    @Override
    public List<Emp> selectAll() throws DataAccessException {
        List<Emp> list = new ArrayList<>();
        try {
            Class.forName(DATABASE_DRIVER);
            conn = DriverManager.getConnection(GLOBLE_DATABASE_EMP_URL,GLOBLE_DATABASE_EMP_USERNAME,GLOBLE_DATABASE_EMP_PASSWORD);
            String sql = new StringBuffer()
                    .append("select id,name,salary,gender,edu,hobbies ")
                    .append(" from t_emp ").toString();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while(rs.next())
            {
                Emp emp = new Emp();
                emp.setId(rs.getInt(1));
                emp.setName(rs.getString(2));
                emp.setSalary(rs.getDouble(3));
                emp.setGender(rs.getString(4));
                emp.setEdu(rs.getString(5));
                emp.setHobbies(rs.getString(6).split(","));
                /*String[] sedu ={"大专","本科","研究生"};
                String[] shobbies = {};*/
                System.out.println(emp);
                list.add(emp);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            throw new DataAccessException("数据库驱动加载异常，请检查依赖");
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataAccessException("数据保存异常");
        }
        return list;
    }

    @Override
    public void updateEmp(Emp emp) throws DataAccessException {
        try {
            Class.forName(DATABASE_DRIVER);
            conn = DriverManager.getConnection(GLOBLE_DATABASE_EMP_URL, GLOBLE_DATABASE_EMP_USERNAME, GLOBLE_DATABASE_EMP_PASSWORD);
            String sql = new StringBuffer()
                    .append("update t_emp set ")
                    .append(" name=?, ")
                    .append(" salary=?, ")
                    .append(" gender=?, ")
                    .append(" edu=?, ")
                    .append(" hobbies=? ")
                    .append(" where id = ?")
                    .toString();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,emp.getName());
            pstmt.setDouble(2,emp.getSalary());
            pstmt.setString(3,emp.getGender());
            pstmt.setString(4,emp.getEdu());
            pstmt.setString(5,emp.getHob());
            pstmt.setInt(6,emp.getId());
            pstmt.executeUpdate();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            throw new DataAccessException("驱动器加载失败异常");
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataAccessException("获取数据库连接异常");
        }
    }

    @Override
    public Emp selectEmpById(Integer i) throws DataAccessException {
        try {
            Class.forName(DATABASE_DRIVER);
            conn = DriverManager.getConnection(GLOBLE_DATABASE_EMP_URL,GLOBLE_DATABASE_EMP_USERNAME,GLOBLE_DATABASE_EMP_PASSWORD);
            String sql = new StringBuffer()
                    .append("select id,name,salary,gender,edu,hobbies ")
                    .append(" from t_emp where id = ?").toString();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,i);
            rs = pstmt.executeQuery();
            Emp emp = new Emp();
            if(rs.next())
            {
                emp.setId(rs.getInt(1));
                emp.setName(rs.getString(2));
                emp.setSalary(rs.getDouble(3));
                emp.setGender(rs.getString(4));
                emp.setEdu(rs.getString(5));
                emp.setHobbies(rs.getString(6).split(","));
                /*String[] sedu ={"大专","本科","研究生"};
                String[] shobbies = {};*/
                System.out.println(emp);
            }
            return emp;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            throw new DataAccessException("数据库驱动加载异常，请检查依赖");
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataAccessException("数据保存异常");
        }
    }

    @Override
    public void deleteEmpById(Integer id) throws DataAccessException {
        try {
            Class.forName(DATABASE_DRIVER);
            conn = DriverManager.getConnection(GLOBLE_DATABASE_EMP_URL,GLOBLE_DATABASE_EMP_USERNAME,GLOBLE_DATABASE_EMP_PASSWORD);
            String sql = new StringBuffer()
                    .append("delete from t_emp where id  = ?").toString();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            pstmt.executeUpdate();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            throw new DataAccessException("数据库驱动加载异常，请检查依赖");
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataAccessException("数据保存异常");
        }
    }
}
